package com.sailfish.springbootdemo.dao.db7;

import com.sailfish.springbootdemo.common.DB7TableName;
import com.sailfish.springbootdemo.pojo.db7.TbKnowledgeBaseDataDTO;
import com.sailfish.springbootdemo.pojo.db7.TbTemplate;
import com.sailfish.springbootdemo.pojo.db7.TempRecord;
import org.apache.ibatis.annotations.*;

import java.util.Collection;
import java.util.List;
import java.util.Map;

public interface TbTemplateDao {

    @Insert("<script>" +
            "insert into " + DB7TableName.TB_TEMPLATE + " " +
            "( "+
            "version, " +
            "template_type, " +
            "parent_id, " +
            "param_name, " +
            "param_name_en, " +
            "param_key, " +
            "parent_param_key, " +
            "param_type, " +
            "level, " +
            "select_level, " +
            "parent_select_id, " +
            "tip, " +
            "remark, " +
            "create_user, " +
            "create_time, " +
            "update_user, " +
            "update_time, " +
            "status, " +
            "sort, " +
            "is_value " +
            ")"+
            "values "+
            "<foreach collection=\"entities\" item=\"entity\" separator=\",\">" +
            "(#{entity.version}, " +
            "#{entity.template_type}, " +
            "#{entity.parent_id}, " +
            "#{entity.param_name}, " +
            "#{entity.param_name_en}, " +
            "#{entity.param_key}, " +
            "#{entity.parent_param_key}, " +
            "#{entity.param_type}, " +
            "#{entity.level}, " +
            "#{entity.select_level}, " +
            "#{entity.parent_select_id}, " +
            "#{entity.tip}, " +
            "#{entity.remark}, " +
            "#{entity.create_user}, " +
            "#{entity.create_time}, " +
            "#{entity.update_user}, " +
            "#{entity.update_time}, " +
            "#{entity.status}, " +
            "#{entity.sort}, " +
            "#{entity.is_value})"+
            "</foreach>"+
            "</script>"
    )
    Integer insertTemplateBatch(@Param("entities") List<TbTemplate> entities);

    @Update("<script>" +

            "update " + DB7TableName.TB_TEMPLATE + " " +
            "set " +
            "version = #{entity.version}, " +
            "template_type = #{entity.template_type}, " +
            "parent_id = #{entity.parent_id}, " +
            "param_name = #{entity.param_name}, " +
            "param_name_en = #{entity.param_name_en}, " +
            "parent_param_key = #{entity.parent_param_key}, " +
            "param_type = #{entity.param_type}, " +
            "level = #{entity.level}, " +
            "tip = #{entity.tip}, " +
            "remark = #{entity.remark}, " +
            "update_user = #{entity.update_user}, " +
            "update_time = #{entity.update_time}, " +
            "status = #{entity.status}, " +
            "sort = #{entity.sort}, " +
            "is_value = #{entity.is_value} " +
            "where id = #{entity.id}" +
            "</script>"
    )
    Integer updateTemplate(@Param("entity") TbTemplate entity);

    @Delete("delete from " + DB7TableName.TB_TEMPLATE + " where id = #{id}"
            )
    Integer deleteById(@Param("id") Integer id);

    @Select("<script>" +
            "SELECT * FROM " + DB7TableName.TB_TEMPLATE + " " +
            "WHERE template_type = #{templateType} " +
            "AND version = #{version} " +
            "AND param_name IN " +
            "<foreach item='item' collection='paramNames' open='(' separator=',' close=')'> " +
            "#{item} " +
            "</foreach>" +
            "</script>")
    List<TbTemplate> getTbTemplateKeysByNames(@Param("templateType") String templateType,
                                              @Param("version") String version,
                                              @Param("paramNames") List<String> paramNames);

    @Select("<script> " +
            "select \n" +
            "*" +
            " from " + DB7TableName.TB_TEMPLATE +
            " where 1=1" +
            "<if test='template_type != null and template_type!=\"\"'>" +
            " and template_type = #{template_type}" +
            "</if>" +
            "<if test='version != null and version!=\"\"'>" +
            " and version = #{version}" +
            "</if>" +
            " order by parent_id asc,id asc" +
            "</script>")
    List<TbTemplate> getTbTemplateByVersion(@Param("template_type") String template_type,
                                            @Param("version") String version
                                            );

    @Select("<script> " +
            "select \n" +
            "param_key" +
            " from " + DB7TableName.TB_TEMPLATE +
            " where 1=1" +
            "<if test='template_type != null and template_type!=\"\"'>" +
            " and template_type = #{template_type}" +
            "</if>" +
            "<if test='version != null and version!=\"\"'>" +
            " and version = #{version}" +
            "</if>" +
            "<if test='paramName != null and paramName!=\"\"'>" +
            " and param_name = #{paramName}" +
            "</if>" +
            " limit 1" +
            "</script>")
    String getTbTemplateKeyByName(@Param("template_type") String template_type,
                                      @Param("version") String version,
                                      @Param("paramName") String paramName
    );


    @Select("<script>" +
            "SELECT param_name " +
            "FROM " + DB7TableName.TB_TEMPLATE +
            " WHERE 1=1" +
            "<if test='templateType != null and templateType != \"\"'>" +
            "  AND template_type = #{templateType}" +
            "</if>" +
            "<if test='parentKey != null and parentKey != \"\"'>" +
            "  AND parent_param_key = #{parentKey}" +
            "</if>" +
            " GROUP BY param_name " +
            " ORDER BY MIN(id)" +
            "</script>")
    List<String> getNameListByParentKey(@Param("templateType") String templateType,
                                        @Param("parentKey") String parentKey);
    @Select("<script> " +
            "select \n" +
            "param_key, param_name, param_type" +
            " from " + DB7TableName.TB_TEMPLATE +
            " where 1=1" +
            "<if test='template_type != null and template_type!=\"\"'>" +
            " and template_type = #{template_type}" +
            "</if>" +
            "<if test='version != null and version!=\"\"'>" +
            " and version = #{version}" +
            "</if>" +
            "<if test='parentKey != null and parentKey!=\"\"'>" +
            " and parent_param_key = #{parentKey}" +
            "</if>" +
            " order by param_key" +
            "</script>")
    List<TbTemplate> getTbTemplateKeyByParentKey(@Param("template_type") String template_type,
                                      @Param("version") String version,
                                      @Param("parentKey") String parentKey
    );
    @Select("<script> " +
            "select \n" +
            "*" +
            " from " + DB7TableName.TB_TEMPLATE +
            " where 1=1" +
            "<if test='template_type != null and template_type!=\"\"'>" +
            " and template_type = #{template_type}" +
            "</if>" +
            "<if test='version != null and version!=\"\"'>" +
            " and version = #{version}" +
            "</if>" +
            "<if test='status != null and status!=\"\"'>" +
            " and status = #{status}" +
            "</if>" +
            " order by sort asc" +
            "</script>")
    List<TbTemplate> getTbTemplateByLimit(@Param("template_type") String template_type,
                                            @Param("version") String version,
                                            @Param("status") Integer status
    );
    @Select("<script> " +
            "select \n" +
            "*" +
            " from " + DB7TableName.TB_TEMPLATE +
            " where 1=1" +
            "<if test='template_type != null and template_type!=\"\"'>" +
            " and template_type = #{template_type}" +
            "</if>" +
            "<if test='version != null and version!=\"\"'>" +
            " and version = #{version}" +
            "</if>" +
            " order by parent_id asc,id asc" +
            "</script>")
    List<TbTemplate> getPublishedTbTemplateByVersion(@Param("template_type") String template_type,
                                            @Param("version") String version
    );

    @Select("<script> " +
            "select \n" +
            "*" +
            " from " + DB7TableName.TB_TEMPLATE +
            " where 1=1" +
            "<if test='template_type != null and template_type!=\"\"'>" +
            " and template_type = #{template_type}" +
            "</if>" +
            "<if test='version != null and version!=\"\"'>" +
            " and version = #{version}" +
            "</if>" +
            "<if test='is_value != null and is_value!=\"\"'>" +
            " and is_value = #{is_value}" +
            "</if>" +
            " order by parent_id asc,id asc" +
            "</script>")
    List<TbTemplate> getTbTemplateByVersion2(@Param("template_type") String template_type,
                                            @Param("version") String version,
                                            @Param("is_value") Integer is_value
    );
    @Select("<script> " +
            "select \n" +
            "*" +
            " from " + DB7TableName.TB_TEMPLATE +
            " where id = #{id}" +
            "</script>")
    TbTemplate getById(@Param("id") Integer id
    );
    @Select("<script> " +
            "select \n" +
            "param_key" +
            " from " + DB7TableName.TB_TEMPLATE +
            " where 1=1" +
            "<if test='template_type != null and template_type!=\"\"'>" +
            " and template_type = #{template_type}" +
            "</if>" +
            "<if test='version != null and version!=\"\"'>" +
            " and version = #{version}" +
            "</if>" +
            "<if test='param_keys != null'>" +
            " AND param_key IN " +
            "<foreach collection='param_keys' item='param_key' open='(' separator=',' close=')'>" +
            " #{param_key}" +
            "</foreach>" +
            "</if>" +
            " AND is_value = 2" +
            "</script>")
    List<String> getNotValueByParamKeys(@Param("template_type") String template_type,
                                             @Param("version") String version,
                                             @Param("param_keys") List<String> param_keys
    );

    @Select("<script> " +
            "SELECT version,param_key " +
            "FROM " + DB7TableName.TB_TEMPLATE +
            " WHERE 1=1 " +
            "<if test='template_type != null and template_type != \"\"'>" +
            " AND template_type = #{template_type} " +
            "</if>" +
            "<if test='versions != null'>" +
            " AND version IN " +
            "<foreach collection='versions' item='version' open='(' separator=',' close=')'>" +
            " #{version} " +
            "</foreach>" +
            "</if>" +
            "<if test='param_keys != null'>" +
            " AND param_key IN " +
            "<foreach collection='param_keys' item='param_key' open='(' separator=',' close=')'>" +
            " #{param_key} " +
            "</foreach>" +
            "</if>" +
            " AND is_value = 2 " +
            "</script>")
    List<TbTemplate> getVersionToNotValueByParamKeys(@Param("template_type") String template_type,
                                                        @Param("versions") Collection<String> versions,
                                                        @Param("param_keys") List<String> param_keys
    );

    @Select("<script> " +
            "select \n" +
            "*" +
            " from " + DB7TableName.TB_TEMPLATE +
            " where 1=1" +
            "<if test='template_type != null and template_type!=\"\"'>" +
            " and template_type = #{template_type}" +
            "</if>" +
            "<if test='version != null and version!=\"\"'>" +
            " and version = #{version}" +
            "</if>" +
            "<if test='param_key != null'>" +
            " AND param_key = #{param_key} " +
            "</if>" +
            "</script>")
    TbTemplate getValueByParamKey(@Param("template_type") String template_type,
                                         @Param("version") String version,
                                         @Param("param_key") String param_key
    );

    @Select("<script> " +
            "select \n" +
            "*" +
            " from " + DB7TableName.TB_TEMPLATE +
            " where 1=1" +
            "<if test='template_type != null and template_type!=\"\"'>" +
            " and template_type = #{template_type}" +
            "</if>" +
            "<if test='version != null and version!=\"\"'>" +
            " and version = #{version}" +
            "</if>" +
            "<if test='param_keys != null'>" +
            " AND parent_param_key IN " +
            "<foreach collection='param_keys' item='param_key' open='(' separator=',' close=')'>" +
            " #{param_key}" +
            "</foreach>" +
            "</if>" +
            "</script>")
    List<TbTemplate> getValuesByParamKeys(@Param("template_type") String template_type,
                                  @Param("version") String version,
                                  @Param("param_keys") List<String> param_keys
    );

    @Select("<script> " +
            "SELECT param_key, parent_param_key " +
            "FROM " + DB7TableName.TB_TEMPLATE +
            " WHERE 1=1" +
            "<if test='template_type != null and template_type != \"\"'>" +
            " AND template_type = #{template_type}" +
            "</if>" +
            "<if test='version != null and version != \"\"'>" +
            " AND version = #{version}" +
            "</if>" +
            "<if test='param_keys != null and param_keys.size > 0'>" +
            " AND param_key IN " +
            "<foreach collection='param_keys' item='param_key' open='(' separator=',' close=')'>" +
            " #{param_key}" +
            "</foreach>" +
            "</if>" +
            "</script>")
    List<TbTemplate> getParentParamKeys(@Param("template_type") String template_type,
                                        @Param("version") String version,
                                        @Param("param_keys") List<String> param_keys);

    @Select("CREATE TEMPORARY TABLE ${tableName} ("
            + "id INT AUTO_INCREMENT PRIMARY KEY, "
            + "knowledge_base_key VARCHAR(255), "
            + "flow_no VARCHAR(255), "
            + "real_param_value JSON, "
            + "INDEX idx_kf (knowledge_base_key, flow_no) "
            + ") ENGINE=InnoDB DEFAULT CHARSET=utf8mb4")
    void createTempTable(@Param("tableName") String tableName);

    @Insert("<script>"
            + "INSERT INTO ${tableName} "
            + "(knowledge_base_key, flow_no, real_param_value) "
            + "VALUES "
            + "<foreach collection='records' item='r' separator=','>"
            + "(#{r.knowledge_base_key}, #{r.flow_no}, #{r.real_param_value})"
            + "</foreach>"
            + "</script>")
    void batchInsertTempTable(@Param("records") List<TempRecord> records, @Param("tableName") String tableName);

    @Select("<script>"
            + "SELECT knowledge_base_key, flow_no "
            + "FROM ${tableName} "
            + "<if test='values != null and values.size() > 0'>"
            + " WHERE ("
            + "  <foreach collection='values' item='val' separator=' OR '>"
            + "   JSON_CONTAINS(real_param_value, CONCAT('\"', #{val}, '\"'))"
            + "  </foreach>"
            + " )"
            + "</if>"
            + " ORDER BY flow_no DESC"
            + "</script>")
    List<TbKnowledgeBaseDataDTO> queryFromTempTable(@Param("values") List<String> values, @Param("tableName") String tableName);

    @Select("DROP TEMPORARY TABLE IF EXISTS ${tableName}")
    void dropTempTable(@Param("tableName") String tableName);


    @Select("<script>"
            + "SELECT param_key, param_name "
            + "FROM tb_template "
            + "WHERE `version` = #{version} "
            + "AND template_type = #{templateType} "
            + "AND param_key IN "
            + "<foreach collection='paramKeys' item='key' open='(' separator=',' close=')'>"
            + "#{key}"
            + "</foreach>"
            + "</script>")
    List<TbTemplate> findParamKeyNamesByKeys(
            @Param("version") String version,
            @Param("templateType") String templateType,
            @Param("paramKeys") List<String> paramKeys
    );
}
